import { Service } from 'egg';
import * as moment from 'moment';
import {OrderedSchedule, OrderedScheduleStatus} from '../../model/ordered_schedule';
import { UserRole } from '../../model/user';

const SQL_QUERY_ORDERED_SCHEDULES_BY_STUDENT =
    `SELECT * FROM ordered_schedule
        WHERE status != ${OrderedScheduleStatus.Deleted} AND student_id = ? AND date >= ?
        ORDER BY date, time_from, time_to`;
const SQL_QUERY_ORDERED_SCHEDULES_BY_TEACHER =
    `SELECT * FROM ordered_schedule
        WHERE status != ${OrderedScheduleStatus.Deleted} AND teacher_id = ? AND date >= ?
        ORDER BY date, time_from, time_to`;

const SQL_QUERY_ORDERED_SCHEDULES_BY_STUDENT_BY_PAGE =
    `SELECT * FROM ordered_schedule
        WHERE student_id = ? AND date >= ?
        ORDER BY date, time_from, time_to LIMIT ? OFFSET ?`;

const SQL_QUERY_ORDERED_SCHEDULES_BY_TEACHER_BY_PAGE =
    `SELECT * FROM ordered_schedule
        WHERE teacher_id = ? AND date >= ?
        ORDER BY date, time_from, time_to LIMIT ? OFFSET ?`;

const SQL_COUNT_ORDERED_SCHEDULES_BY_STUDENT =
    'SELECT count(*) FROM ordered_schedule WHERE student_id = ? AND date >= ?';

const SQL_COUNT_ORDERED_SCHEDULES_BY_TEACHER =
    `SELECT count(*) FROM ordered_schedule WHERE teacher_id = ? AND date >= ?`;

const BY_PAGE_ORDER_BY_DATETIME_DESC =
    ` ORDER BY os.date DESC, os.time_from DESC, os.time_to DESC LIMIT ? OFFSET ?`;
const BY_PAGE_ORDER_BY_DATETIME_ASC =
    ` ORDER BY os.date ASC, os.time_from ASC, os.time_to ASC LIMIT ? OFFSET ?`;
const BY_PAGE_ORDER_BY_TEACHER_NAME_DESC =
    ` ORDER BY ut.username DESC LIMIT ? OFFSET ?`;
const BY_PAGE_ORDER_BY_TEACHER_NAME_ASC =
    ` ORDER BY ut.username ASC LIMIT ? OFFSET ?`;
const BY_PAGE_ORDER_BY_STUDENT_NAME_DESC =
    ` ORDER BY us.username DESC LIMIT ? OFFSET ?`;
const BY_PAGE_ORDER_BY_STUDENT_NAME_ASC =
    ` ORDER BY us.username ASC LIMIT ? OFFSET ?`;
const BY_PAGE_ORDER_BY_STATUS_DESC =
    ` ORDER BY os.status DESC LIMIT ? OFFSET ?`;
const BY_PAGE_ORDER_BY_STATUS_ASC =
    ` ORDER BY os.status ASC LIMIT ? OFFSET ?`;

export enum OrderBy {
    DATETIME_DESC = 0,
    DATETIME_ASC = 1,
    TEACHER_NAME_DESC = 2,
    TEACHER_NAME_ASC = 3,
    STUDENT_NAME_DESC = 4,
    STUDENT_NAME_ASC = 5,
    STATUS_DESC = 6,
    STATUS_ASC = 7,
}

export default class DbOrderedScheduleService extends Service {
    public async getSameClassByStudent(date: string, timeFrom: string, timeTo: string, studentId: number, mysql?: any) {
        if (!mysql) {
            mysql = this.app.mysql;
        }
        const schedules =
            await mysql.query(`SELECT * FROM ordered_schedule
                                    WHERE status != ${OrderedScheduleStatus.Deleted}
                                        AND student_id = ?
                                        AND date >= ?
                                        AND time_from = ?
                                        AND time_to = ?
                                    ORDER BY date, time_from, time_to`,
                [studentId, date, timeFrom, timeTo]);
        if (!schedules) {
            return [];
        }
        return schedules;
    }

    public async getOrderedSchedulesByStudent(studentId: number, mysql?: any): Promise<OrderedSchedule[]> {
        if (!mysql) {
            mysql = this.app.mysql;
        }
        const schedules =
            await mysql.query(SQL_QUERY_ORDERED_SCHEDULES_BY_STUDENT, [studentId, this.ctx.helper.todayDate()]);
        if (!schedules) {
            return [];
        }
        return schedules;
    }

    public async getAllByTeacher(teacherId: number): Promise<OrderedSchedule[]> {
        const mysql = this.app.mysql;
        const schedules =
            await mysql.query(SQL_QUERY_ORDERED_SCHEDULES_BY_TEACHER, [teacherId, this.ctx.helper.todayDate()]);
        if (!schedules) {
            return [];
        }
        return schedules;
    }

    public async getByTeacherAndPage(teacherId: number, page: number, pageSize: number):
        Promise<OrderedSchedule[]> {
        const mysql = this.app.mysql;
        const schedules = await mysql.query(SQL_QUERY_ORDERED_SCHEDULES_BY_TEACHER_BY_PAGE,
            [teacherId, this.ctx.helper.todayDate(), pageSize, (page - 1) * pageSize]);
        if (!schedules) {
            return [];
        }
        return schedules;
    }

    public async getByStudentAndPage(studentId: number, page: number, pageSize: number, mysql?: any):
        Promise<OrderedSchedule[]> {
        if (!mysql) {
            mysql = this.app.mysql;
        }
        const schedules = await mysql.query(SQL_QUERY_ORDERED_SCHEDULES_BY_STUDENT_BY_PAGE,
                [studentId, this.ctx.helper.todayDate(), pageSize, (page - 1) * pageSize]);
        if (!schedules) {
            return [];
        }
        return schedules;
    }

    public async getCountByStudent(studentId: number) {
        const mysql = this.app.mysql;
        const result = await mysql.queryOne(SQL_COUNT_ORDERED_SCHEDULES_BY_STUDENT,
            [studentId, this.ctx.helper.todayDate()]);
        return result['count(*)'];
    }

    public async getCountByTeacher(teacherId: number) {
        const mysql = this.app.mysql;
        const result = await mysql.queryOne(SQL_COUNT_ORDERED_SCHEDULES_BY_TEACHER,
            [teacherId, this.ctx.helper.todayDate()]);
        return result['count(*)'];
    }

    public async findOneById(id: number, mysql?: any) {
        if (!mysql) {
            mysql = this.app.mysql;
        }
        const result = mysql.get('ordered_schedule', { id });
        if (result) {
            return result as OrderedSchedule;
        }
        return null;
    }

    public async removeOneById(id: number, mysql?: any): Promise<boolean> {
        if (!mysql) {
            mysql = this.app.mysql;
        }
        const result = await mysql.delete('ordered_schedule', { id });
        if (result.affectedRows === 1) {
            return true;
        }
        return false;
    }

    public async update(newData: any, mysql?: any) {
        if (!mysql) {
            mysql = this.app.mysql;
        }
        await mysql.update('ordered_schedule', {
            updated_at: mysql.literals.now,
            ...newData,
        });
    }

    public async create(data: any, mysql?: any) {
        if (!mysql) {
            mysql = this.app.mysql;
        }
        await mysql.insert('ordered_schedule', {
            updated_at: mysql.literals.now,
            created_at: mysql.literals.now,
            ...data,
        });
    }

    public async getByDate(teacherIds: number[], startDate: string, endDate: string) {
        const mysql = this.app.mysql;
        if (!teacherIds || teacherIds.length <= 0) {
            return await mysql.query(
                `SELECT
                    o.id as id,
                    t.id as teacher_id,
                    o.student_id as student_id,
                    t.username as teacher_name,
                    s.username as student_name,
                    t.phone as teacher_phone,
                    s.phone as student_phone,
                    o.created_at as created_at,
                    o.updated_at as updated_at,
                    o.date as date,
                    o.time_from as time_from,
                    o.time_to as time_to,
                    o.status as status
                FROM user as t 
                INNER JOIN ordered_schedule as o ON o.teacher_id = t.id AND o.date  >= ? AND o.date < ?
                INNER JOIN user as s ON o.student_id = s.id
                WHERE t.role = ${UserRole.Teacher}
                ORDER BY t.id ASC, o.date ASC, o.time_from ASC, o.time_to ASC;`, [startDate, endDate]);
        }
        return await mysql.query(
            `SELECT
                o.id as id,
                t.id as teacher_id,
                o.student_id as student_id,
                t.username as teacher_name,
                s.username as student_name,
                t.phone as teacher_phone,
                s.phone as student_phone,
                o.created_at as created_at,
                o.updated_at as updated_at,
                o.date as date,
                o.time_from as time_from,
                o.time_to as time_to,
                o.status as status
            FROM user as t 
            LEFT JOIN ordered_schedule as o ON o.teacher_id = t.id AND o.date  >= ? AND o.date < ?
            LEFT JOIN user as s ON o.student_id = s.id
            WHERE t.role = ${UserRole.Teacher} AND t.id in (${teacherIds.join(',')})
            ORDER BY t.id ASC, o.date ASC, o.time_from ASC, o.time_to ASC;`, [startDate, endDate]);
    }

    public async getCount(dateStart?: string, dateEnd?: string, userId?: string): Promise<number> {
        const mysql = this.app.mysql;
        let sql = `SELECT count(*) FROM ordered_schedule `;
        if (dateStart && dateEnd) {
            sql += ` WHERE date >= '${dateStart}' AND date <= '${dateEnd}' `;
        } else if (dateStart) {
            sql += ` WHERE date >= '${dateStart}' `;
        } else if (dateEnd) {
            sql += ` WHERE date <= '${dateEnd}'`;
        }

        if (userId && userId !== '0') {
            sql += ` AND (student_id = '${userId}' OR teacher_id = '${userId}' )`;
        }
        const result = await mysql.queryOne(sql);
        return result['count(*)'];
    }

    public async getByPage(page: number, pageSize: number, orderBy: OrderBy,
        dateStart?: string, dateEnd?: string, userId?: string):
        Promise<OrderedSchedule[]> {
        let sql = `SELECT
                    os.id as id,
                    ut.id as teacher_id,
                    ut.username as teacher_name,
                    us.id as student_id,
                    us.username as student_name,
                    os.date as date,
                    os.time_from as time_from,
                    os.time_to as time_to,
                    os.status as status,
                    os.created_at as created_at,
                    os.updated_at as updated_at,
                    os.clocked as clocked,
                    os.clocked_at as clocked_at
                FROM ordered_schedule as os
                INNER JOIN user as ut
                ON ut.id = os.teacher_id
                INNER JOIN user as us
                ON us.id = os.student_id`;
        if (dateStart && dateEnd) {
            sql += ` WHERE date >= '${dateStart}' AND date <= '${dateEnd}' `;
        } else if (dateStart) {
            sql += ` WHERE date >= '${dateStart}' `;
        } else if (dateEnd) {
            sql += ` WHERE date <= '${dateEnd}' `;
        }

        if (userId && userId !== '0') {
            sql += ` AND (os.student_id = '${userId}' OR os.teacher_id = '${userId}')`;
        }
        this.app.logger.info(sql);
        switch (orderBy) {
            case OrderBy.DATETIME_DESC:
                sql += BY_PAGE_ORDER_BY_DATETIME_DESC;
                break;
            case OrderBy.DATETIME_ASC:
                sql += BY_PAGE_ORDER_BY_DATETIME_ASC;
                break;
            case OrderBy.TEACHER_NAME_DESC:
                sql += BY_PAGE_ORDER_BY_TEACHER_NAME_DESC;
                break;
            case OrderBy.TEACHER_NAME_ASC:
                sql += BY_PAGE_ORDER_BY_TEACHER_NAME_ASC;
                break;
            case OrderBy.STUDENT_NAME_DESC:
                sql += BY_PAGE_ORDER_BY_STUDENT_NAME_DESC;
                break;
            case OrderBy.STUDENT_NAME_ASC:
                sql += BY_PAGE_ORDER_BY_STUDENT_NAME_ASC;
                break;
            case OrderBy.STATUS_DESC:
                sql += BY_PAGE_ORDER_BY_STATUS_DESC;
                break;
            case OrderBy.STATUS_ASC:
                sql += BY_PAGE_ORDER_BY_STATUS_ASC ;
                break;
            default:
                break;
        }

        const mysql = this.app.mysql;
        const schedules = await mysql.query(sql,
            [ pageSize, (page - 1) * pageSize]);
        if (!schedules) {
            return [];
        }
        return schedules.map((item) => ({
            ...item,
            date: moment(item.date).format('YYYY-MM-DD'),
            time_from: moment(item.time_from, 'HH:mm:SS').format('HH:mm'),
            time_to: moment(item.time_to, 'HH:mm:SS').format('HH:mm'),
        }));
    }

    public async get30Minites() {
        const mysql = this.app.mysql;
        const now = moment().add(30, 'minutes');
        const date = now.format('YYYY-MM-DD');
        const timeFromStart = now.format('HH:mm');
        const timeFromEnd = now.add(5, 'minutes').format('HH:mm');
        return await mysql.query(`SELECT
                                    os.id as id,
                                    ut.id as teacher_id,
                                    ut.username as teacher_name,
                                    ut.phone as teacher_phone,
                                    us.id as student_id,
                                    us.username as student_name,
                                    us.phone as student_phone,
                                    us.admin_id as admin_id,
                                    os.date as date,
                                    os.time_from as time_from,
                                    os.time_to as time_to,
                                    os.status as status,
                                    os.created_at as created_at,
                                    os.updated_at as updated_at
                                FROM ordered_schedule as os
                                INNER JOIN user as ut
                                ON ut.id = os.teacher_id
                                INNER JOIN user as us
                                ON us.id = os.student_id 
                                WHERE os.status = 0 AND date = ? AND time_from >= ? AND time_from < ?`,
                [date, timeFromStart, timeFromEnd]);
    }

    public async get1Hour() {
        const mysql = this.app.mysql;
        const now = moment().add(1, 'hours');
        const date = now.format('YYYY-MM-DD');
        const timeFromStart = now.format('HH:mm');
        const timeFromEnd = now.add(5, 'minutes').format('HH:mm');
        return await mysql.query(`SELECT
                                    os.id as id,
                                    ut.id as teacher_id,
                                    ut.username as teacher_name,
                                    ut.phone as teacher_phone,
                                    us.id as student_id,
                                    us.username as student_name,
                                    us.phone as student_phone,
                                    us.admin_id as admin_id,
                                    os.date as date,
                                    os.time_from as time_from,
                                    os.time_to as time_to,
                                    os.status as status,
                                    os.created_at as created_at,
                                    os.updated_at as updated_at
                                FROM ordered_schedule as os
                                INNER JOIN user as ut
                                ON ut.id = os.teacher_id
                                INNER JOIN user as us
                                ON us.id = os.student_id 
                                WHERE os.status = 0 AND date = ? AND time_from >= ? AND time_from < ?`,
                [date, timeFromStart, timeFromEnd]);
    }

    public async getTomorrow() {
        const mysql = this.app.mysql;
        const now = moment().add(24, 'hours');
        const date = now.format('YYYY-MM-DD');
        const timeFromStart = now.format('HH:mm');
        const timeFromEnd = now.add(5, 'minutes').format('HH:mm');
        return await mysql.query(`SELECT
                                    os.id as id,
                                    ut.id as teacher_id,
                                    ut.username as teacher_name,
                                    ut.phone as teacher_phone,
                                    us.id as student_id,
                                    us.username as student_name,
                                    us.phone as student_phone,
                                    us.admin_id as admin_id,
                                    os.date as date,
                                    os.time_from as time_from,
                                    os.time_to as time_to,
                                    os.status as status,
                                    os.created_at as created_at,
                                    os.updated_at as updated_at
                                FROM ordered_schedule as os
                                INNER JOIN user as ut
                                ON ut.id = os.teacher_id
                                INNER JOIN user as us
                                ON us.id = os.student_id 
                                WHERE os.status = 0 AND date = ? AND time_from >= ? AND time_from < ?`,
                [date, timeFromStart, timeFromEnd]);
    }
}
